﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using YiSha.Util;
using YiSha.Util.Model;

namespace YiSha.SqlSugars
{
    public class SugarsDatabase : IAdapter
    {
        #region 属性
        /// <summary>
        /// 获取 当前使用的数据访问上下文对象
        /// </summary>
        public SqlSugarClient dbContext { get; set; }
        #endregion
        #region 构造函数
        /// <summary>
        /// 构造方法
        /// </summary>
        public SugarsDatabase()
        {
            if (dbContext == null)
            {
                dbContext = SugarBase.GetInstance();
            }
        }
        #endregion


        #region 添加
        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert<T>(T entity) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Insertable<T>(entity).IgnoreColumns(ignoreNullColumn: true).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        /// <returns></returns>
        public bool Insert<T>(List<T> entityList) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Insertable<T>(entityList.ToList<T>()).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T">新增实体</typeparam>
        /// <typeparam name="TResult">扩展实体</typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public object Insert<T, TResult>(TResult entity) where T : class, new()
        {
            object result;
            try
            {
                T entity2 = DictionaryMapUtils.Mapping<T, TResult>(entity);
                result = dbContext.Insertable<T>(entity2).IgnoreColumns(ignoreNullColumn: true).RemoveDataCache().ExecuteReturnEntity();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rowObj">rowObj为匿名对象时只更新指定列( 例如:new{ name='abc'}只更新name )，为T类型将更新整个实体(排除主键、自增列和禁止更新列)</param>
        /// <param name="filterExpression">过滤条件</param>
        /// <returns></returns>
        public bool Update<T>(object rowObj, Expression<Func<T, bool>> filterExpression) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Updateable<T>(rowObj).Where(filterExpression).IgnoreColumns(ignoreAllNullColumns: true).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateDic">更新字段</param>
        /// <param name="filterExpression">过滤条件</param>
        /// <returns></returns>
        public bool Update<T>(T updateDic, Expression<Func<T, bool>> filterExpression) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Updateable<T>(updateDic).Where(filterExpression).IgnoreColumns(ignoreAllNullColumns: true).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 修改指定的行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateDic">更新字段</param>
        /// <param name="filterExpression">过滤条件</param>
        /// <returns></returns>
        public bool UpdateColumns<T>(T rowObj, Expression<Func<T, object>> columns) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Updateable(rowObj).UpdateColumns(columns).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 更新数据【排除指定的字段】
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateDic"></param>
        /// <param name="filterExpression"></param>
        /// <returns></returns>
        public bool UpdateIgnoreCol<T>(T updateDic, Expression<Func<T, object>> columns) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Updateable(updateDic).IgnoreColumns(columns).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 根据主键跟新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateDic"></param>
        /// <returns></returns>
        public bool Update<T>(T updateDic) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Updateable<T>(updateDic).IgnoreColumns(ignoreAllNullColumns: true).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parm"></param>
        /// <returns></returns>
        public bool Update<T>(List<T> parm) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Updateable<T>(parm).IgnoreColumns(ignoreAllNullColumns: true).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateExpression">更新字段</param>
        /// <param name="filterExpression">过滤条件</param>
        /// <returns></returns>
        public bool Update<T>(Expression<Func<T, T>> updateExpression, Expression<Func<T, bool>> filterExpression) where T : class, new()
        {
            bool result;
            try
            {
                Dictionary<string, string> updateDic = GetUpdateDic<T>(updateExpression);
                result = dbContext.Updateable<T>(updateDic).Where(filterExpression).IgnoreColumns(ignoreAllNullColumns: true).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region 查询
        /// <summary>
        /// 查询单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">Lamda 表达式</param>
        /// <returns></returns>
        public T GetEntity<T>(Expression<Func<T, bool>> where) where T : class, new()
        {
            T result;
            try
            {
                result = dbContext.Queryable<T>().Where(where).First();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 查询单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">Lamda 表达式</param>
        /// <param name="expression">查询字段</param>
        /// <returns></returns>
        public T GetEntity<T>(Expression<Func<T, bool>> where, Expression<Func<T, T>> expression) where T : class, new()
        {
            T result;
            try
            {
                result = dbContext.Queryable<T>().Where(where).Select<T>(expression).First();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 根据主键值获取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public T GetById<T>(object value) where T : class, new()
        {
            T result;
            try
            {
                result = dbContext.Queryable<T>().InSingle(value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 根据主键值获取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <param name="expression">查询字段</param>
        /// <returns></returns>
        public T GetById<T>(object value, Expression<Func<T, T>> expression) where T : class, new()
        {
            T result;
            try
            {
                result = dbContext.Queryable<T>().Select<T>(expression).InSingle(value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 查询对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">Lamda 表达式</param>
        /// <returns></returns>
        public List<T> GetList<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            List<T> result;
            try
            {
                result = dbContext.Queryable<T>().Where(expression).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 查询对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> GetList<T>() where T : class, new()
        {
            List<T> result;
            try
            {
                result = dbContext.Queryable<T>().ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 查询对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">Lamda 表达式</param>
        /// <param name="expression">查询字段</param>
        /// <returns></returns>
        public List<T> GetList<T>(Expression<Func<T, bool>> where, Expression<Func<T, T>> expression) where T : class, new()
        {
            List<T> result;
            try
            {
                result = dbContext.Queryable<T>().Where(where).Select<T>(expression).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion
        #region 分页查询
        /// <summary>
        /// 查询分页对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">条件表达式</param>
        /// <param name="pagination">分页信息，注意这里已经计算当前页，不需要再次计算</param>
        /// <returns></returns>
        public Tuple<int, List<T>> GetPager<T>(Expression<Func<T, bool>> expression, Pagination pagination) where T : class, new()
        {
            Tuple<int, List<T>> result;
            try
            {
                int item = 0;
                List<T> item2 = dbContext.Queryable<T>().Where(expression).OrderBy(pagination.Sort + " " + pagination.SortType).ToPageList(pagination.PageIndex, pagination.PageSize, ref item);
                result = new Tuple<int, List<T>>(item, item2);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filterExpression">过滤条件</param>
        /// <returns></returns>
        public bool Delete<T>(Expression<Func<T, bool>> filterExpression) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Deleteable<T>(filterExpression).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        /// <returns></returns>
        public bool Delete<T>(List<T> entityList) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Deleteable<T>(entityList.ToList<T>()).RemoveDataCache().ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region 执行sql语句
        /// <summary>
        /// Sql语句查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">执行语句</param>
        /// <param name="rowObj">查询表达式 例如：new{id=1,name=2}</param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, object rowObj = null) where T : class, new()
        {
            List<T> list;
            try
            {
                if (rowObj.IsEmpty())
                {
                    list = dbContext.Ado.SqlQuery<T>(sql);
                }
                else
                {
                    list = dbContext.Ado.SqlQuery<T>(sql, rowObj);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        /// <summary>
        /// SQL查询总条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">执行语句</param>
        /// <param name="rowObj">查询表达式 例如：new{id=1,name=2}</param>
        /// <returns></returns>
        public int SqlQueryCount(string sql, object rowObj = null)
        {
            int count;
            try
            {
                count = dbContext.Ado.GetInt(sql, rowObj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }
        #endregion

        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public bool Exists<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            bool result;
            try
            {
                result = dbContext.Queryable<T>().Any(expression);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 统计
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public int Count<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            int result;
            try
            {
                result = dbContext.Queryable<T>().Where(expression).Count();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// SQL数据查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">执行语句</param>
        /// <param name="rowObj">条件表达式</param>
        /// <returns></returns>
        public List<T> GetList<T>(string sql, object rowObj = null) where T : class, new()
        {
            List<T> list;
            try
            {
                list = dbContext.Ado.SqlQuery<T>(sql, rowObj).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        /// <summary>
        ///  SQL分页数据查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="rowObj">参数</param>
        /// <param name="pagination">分页信息</param>
        /// <returns></returns>
        public Tuple<int, List<T>> GetPager<T>(string sql, Dictionary<string, object> pairs = null, Pagination pagination = null) where T : class, new()
        {
            Tuple<int, List<T>> result;
            try
            {
                int item = 0;
                List<T> item2 = new List<T>();
                if (pairs.Count > 0)
                {
                    item2 = dbContext.SqlQueryable<T>(sql).AddParameters(pairs).OrderBy(pagination.Sort + " " + pagination.SortType).ToPageList(pagination.PageIndex, pagination.PageSize, ref item);
                }
                else
                {
                    item2 = dbContext.SqlQueryable<T>(sql).OrderBy(pagination.Sort + " " + pagination.SortType).ToPageList(pagination.PageIndex, pagination.PageSize, ref item);
                }
                result = new Tuple<int, List<T>>(item, item2);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        ///  SQL分页数据查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="pagination">分页信息</param>
        /// <returns></returns>
        public Tuple<int, List<T>> GetPager<T>(string sql, Pagination pagination = null) where T : class, new()
        {
            Tuple<int, List<T>> result;
            try
            {
                int item = 0;
                List<T> item2 = dbContext.SqlQueryable<T>(sql).OrderBy(pagination.Sort + " " + pagination.SortType).ToPageList(pagination.PageIndex, pagination.PageSize, ref item);
                result = new Tuple<int, List<T>>(item, item2);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 执行SQL返回受影响行数
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="rowObj">条件表达式</param>
        /// <returns></returns>
        public int ExecuteBySql(string sql, object rowObj = null)
        {
            int result;
            try
            {
                result = dbContext.Ado.ExecuteCommand(sql, rowObj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        /// <summary>
        /// 获取第一行第一列
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="rowObj">条件表达式</param>
        /// <returns></returns>
        public object GetScalar(string sql, object rowObj = null)
        {
            object scalar;
            try
            {
                scalar = dbContext.Ado.GetScalar(sql, rowObj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return scalar;
        }

        /// <summary>
        /// 执行表达式的方法
        /// </summary>
        /// <param name="body"></param>
        /// <returns></returns>
        private static object ExecutionLambda(Expression body)
        {
            return Expression.Lambda(body, Array.Empty<ParameterExpression>()).Compile().DynamicInvoke(Array.Empty<object>());
        }

        /// <summary>
        /// 获取更新字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateExpression"></param>
        /// <returns></returns>
        private static Dictionary<string, string> GetUpdateDic<T>(Expression<Func<T, T>> updateExpression) where T : class
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>();
            MemberInitExpression memberInitExpression = updateExpression.Body as MemberInitExpression;
            if (memberInitExpression == null)
            {
                throw new ArgumentException("更新表达式的类型必须是MemberInitExpression.", "updateExpression");
            }
            foreach (MemberBinding memberBinding in memberInitExpression.Bindings)
            {
                string name = memberBinding.Member.Name;
                MemberAssignment memberAssignment = memberBinding as MemberAssignment;
                if (memberAssignment == null)
                {
                    throw new ArgumentException("更新表达式的MemberBinding必须是MemberAssignment.", "updateExpression");
                }
                object value;
                if (memberAssignment.Expression.NodeType == ExpressionType.Constant)
                {
                    ConstantExpression constantExpression = memberAssignment.Expression as ConstantExpression;
                    if (constantExpression == null)
                    {
                        throw new ArgumentException("MemberAssignment表达式不是一个ConstantExpression.", "updateExpression");
                    }
                    if (constantExpression.Value is Enum)
                    {
                        value = (int)constantExpression.Value;
                    }
                    else
                    {
                        value = constantExpression.Value;
                    }
                }
                else
                {
                    value = ExecutionLambda(memberAssignment.Expression);
                }
                dictionary.Add(name, Convert.ToString(value));
            }
            return dictionary;
        }
        #region 事务
        /// <summary>
        /// 带事务处理的执行数据库操作
        /// </summary>
        /// <param name="func">方法委托</param>
        /// <returns></returns>
        public bool ExcuteTransaction(Func<SqlSugarClient, bool> func)
        {
            bool result;
            try
            {
                using (SqlSugarClient instance = SugarBase.GetInstance(30))
                {
                    instance.Ado.CommandTimeOut = 30000;
                    try
                    {
                        instance.BeginTran();
                        if (func(instance))
                        {
                            instance.CommitTran();
                            result = true;
                        }
                        else
                        {
                            instance.RollbackTran();
                            result = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        instance.RollbackTran();
                        result = false;
                    }
                    finally
                    {
                        instance.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <typeparam name="Result"></typeparam>
        /// <param name="func"></param>
        /// <param name="commandTimeOut"></param>
        /// <returns></returns>
        public Result ExecTran<Result>(Func<SqlSugarClient, Result> func, int commandTimeOut = 30)
        {
            if (func == null)
            {
                throw new Exception("委托为null, 事务处理无意义");
            }
            Result result2;
            using (SqlSugarClient sqlSugarClient = SugarBase.InitDB(commandTimeOut))
            {
                try
                {
                    sqlSugarClient.Ado.BeginTran(IsolationLevel.Unspecified);
                    Result result = func(sqlSugarClient);
                    sqlSugarClient.CommitTran();
                    result2 = result;
                }
                catch (Exception ex)
                {
                    sqlSugarClient.RollbackTran();
                    throw ex;
                }
                finally
                {
                    sqlSugarClient.Dispose();
                }
            }
            return result2;
        }

        #endregion
    }
}
